# coding: utf-8
import urllib2

from alipay import AliPay
from django.http import HttpResponseRedirect, HttpResponse
from djangoext.mako import render_to_response , render_to_string, render_string

import json
from shangjie.utils import log, cache
from shangjie.utils import lunar
from shangjie.conf import settings
from sqlalchemy.sql import *


from django.http import HttpResponse
from apiUtils import *
from oa.commons.baseinfo import *
from oa.utils import *
from oaconf import LOGDIR

import sys
import os
reload(sys)
sys.setdefaultencoding('utf-8')
from log import LOG

obj = LOG( name = 'viewsinfo' ,logdir = LOGDIR )
log = obj.init_logger()


try:
    import uuid
except:
    import fake_uuid as uuid

# 用户注册
def register(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                                content_type='application/json; charset=utf-8')
        # 获取请求要素
        phonenum = reqobj.get('strPhone', '').replace(' ', '')
        checkcode = reqobj.get('strCheckcode', '')
        password = reqobj.get('strPasswd', '')
        userType = reqobj.get('strType', '1')
        # 初始化反馈信息
        rspcode, rspinfo = 0, 'OK'

        rspcode, rspinfo = check_code_valid(checkcode, phonenum)
        if rspcode != 0:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': rspcode, 'rspinfo': rspinfo}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        se = settings.DB_SESSION()
        if userType=='0':
            #物业人员
            check_exited_sql="""select * from zhsq_employee where mobile='%s' and  eState !=2""" % phonenum
            check_exited = se.execute(check_exited_sql.decode('utf-8')).fetchall()
            if check_exited:
                se.close()
                return HttpResponse(sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '手机号码已注册'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            else:
                eid = 'EID%s' % datetime.datetime.now().strftime("%Y%m%d%H%M%S")
                passwd = get_md5(password)
                insert_sql = """insert into zhsq_employee(eid, mobile, `password`, eState, eHireDate, bz3,integral) values ('%s', '%s', '%s', '-1' ,'%s', '%s','0')""" % (
                    eid, phonenum, passwd, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                    datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
                se.execute(insert_sql.decode('utf-8'))
                user_sql = """select * from zhsq_employee where mobile='%s'""" % phonenum
                user_info = se.execute(user_sql.decode('utf-8')).fetchone()
                result = {'strPid': eid, 'strUserid': str(user_info._id), "strPhone": user_info.mobile, "strType": '0',
                          "strStatus": '-1', 'strCommunityId': user_info.cid,'strIntegral':user_info.integral}
        elif userType=='1':
            # 业主
            check_exited_sql = """select * from zhsq_owner where pMobile='%s' and status !=1""" % phonenum
            check_exited = se.execute(check_exited_sql.decode('utf-8')).fetchall()
            if check_exited:
                se.close()
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '手机号码已注册'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            else:
                pid = 'PID%s' % datetime.datetime.now().strftime("%Y%m%d%H%M%S")
                passwd = get_md5(password)
                insert_sql = """insert into zhsq_owner(pid, pMobile, `password`, `status`, registertime, lastlogintime,integral) values ('%s', '%s', '%s', '-1', '%s', '%s','0')""" % (pid, phonenum, passwd, datetime.datetime.now().strftime( '%Y-%m-%d %H:%M:%S' ), datetime.datetime.now().strftime( '%Y-%m-%d %H:%M:%S' ))
                se.execute(insert_sql.decode('utf-8'))
                user_sql = """select * from zhsq_owner where pMobile='%s'""" % phonenum
                user_info = se.execute(user_sql.decode('utf-8')).fetchone()
                result = {'strPid':pid, 'strUserid': str(user_info._id), "strPhone": user_info.pMobile, "strType": '1', "strStatus":'-1', 'strCommunityId':user_info.communityId,'strIntegral':user_info.integral}
        else:
            se.close()
            return HttpResponse(sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '用户类型不支持'}, ensure_ascii=False, encoding='utf-8'),
                                content_type='application/json; charset=utf-8')

        tokentime = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        validtime_sql = """select paramvalue from api_parameter where paramcode='TokenTimeout'"""
        validtime = se.execute(validtime_sql.decode('utf-8')).fetchone().paramvalue
        token_insert = """insert into api_token(userid, createtime, timelength, usertype) values ('%s', '%s', '%s', '%s')""" % (
            user_info._id, tokentime, validtime, userType)
        se.execute(token_insert.decode('utf-8'))
        token_sql = """select * from api_token where userid='%s' and usertype='%s'""" % (user_info._id, userType)
        token = se.execute(token_sql.decode('utf-8')).fetchone().tokenid
        result['strToken'] = str(token)

        se.close()
        return HttpResponse(sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': result}, ensure_ascii=False, encoding='utf-8'),
                                content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '请求处理异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def login(req):
    try:
        se = settings.DB_SESSION()
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        phonenum = reqobj.get('strPhone', '').replace(' ', '')
        password = reqobj.get('strPasswd', '')
        userType = reqobj.get('strType', '1')

        rspcode, rspinfo = 0, 'OK'

        passwd = get_md5(password)

        if userType == '0':
            user_sql = """select * from zhsq_employee where mobile='%s'  and eState !='2'""" % phonenum
            res = se.execute(user_sql.decode('utf-8')).fetchone()
            if not res:
                se.close()
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '用户不存在！'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            if res.password != passwd:
                se.close()
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '密码错误！'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            result = {'strPid':res.eid, 'strUserid': str(res._id), 'strPhone': res.mobile, 'strType': '0', 'strStatus': res.eState, 'strName': res.eName,
                   'strCardNum': res.certNo, 'strSex': res.eSex or 'M', 'strEmail': res.email, 'strCompanyName': res.companyName, 'strCompanyId':res.companyId, 'strCommunityId':res.cid,'strIntegral':res.integral}
            try:
                select_sql = """select * from zhsq_community where communityVettedId = '%s'""" % (res.cid)
                res = se.execute(select_sql.decode('utf-8')).fetchone()
                result['strCommunityName'] = res.comName
            except:
                result['strCommunityName']=''
        else:
            user_sql = """select * from zhsq_owner where pMobile='%s'  and status !='1'""" % phonenum
            res = se.execute(user_sql.decode('utf-8')).fetchone()
            if not res:
                se.close()
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '用户不存在！'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            if res.password != passwd:
                se.close()
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '密码错误！'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            result = {'strPid':res.pid, 'strUserid': str(res._id), 'strPhone': res.pMobile, 'strType': '1', 'strStatus': res.status,
                   'strName': res.pName,
                   'strCardNum': res.pCardNum, 'strSex': res.pSex or 'M', 'strCompanyName': res.companyName, 'strCommunityId':res.communityId, 'strCommunityName':res.communityName, 'strCompanyId':res.pcid,'strIntegral':res.integral}
            try:
                house_sql = """select distinct communityId from zhsq_house2owner where pid='%s' and bz2 !='0'""" % res.pid
                house = se.execute(house_sql.decode('utf-8')).fetchall()
                row = {}
                for i in range(len(house)):
                    key = 'community%s'%i
                    row[key] = house[i][0]
                result['arrCommunityInfo'] = row
            except:
                result['arrCommunityInfo'] = ''

        token_sql = """select * from api_token where userid='%s' and usertype='%s'""" % (result['strUserid'], userType)
        token = se.execute(token_sql.decode('utf-8')).fetchone().tokenid
        dqsj = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        token_update = """update api_token set createtime='%s' where tokenid=%s""" % (dqsj, int(token))
        se.execute(token_update.decode('utf-8'))
        result['strToken'] = str(token)

        se.close()
        return HttpResponse(
                sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': result}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '服务器处理异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')



def userinfo(req):
    try:
        se = settings.DB_SESSION()
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        userId = reqobj.get('strId', '')
        token = reqobj.get('strToken', '-1')
        userType = reqobj.get('strType', '1')

        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        if userType == '0':
            user_sql = """select * from zhsq_employee where _id='%s'""" % userId
            res = se.execute(user_sql.decode('utf-8')).fetchone()
            result = {'strPid': res.eid, 'strUserid': str(res._id), 'strPhone': res.mobile, 'strType': '0', 'strStatus': res.eState, 'strName': res.eName if res.eName else '',
                   'strCardNum': res.certNo if res.certNo else '', 'strSex': res.eSex or 'M', 'strEmail': res.email if res.email else '', 'strCompanyName': res.companyName if res.companyName else '', 'strCommunityId': res.cid if res.cid else '', 'strCompanyId': res.companyId if res.companyId else '','strIntegral':res.integral}
            try:
                select_sql = """select * from zhsq_community where communityVettedId = '%s'""" % (res.cid)
                res = se.execute(select_sql.decode('utf-8')).fetchone()
                result['strCommunityName'] = res.comName
            except:
                result['strCommunityName'] = ''
            se.close()
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': result}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        else:
            user_sql = """select * from zhsq_owner where _id='%s'""" % userId
            res = se.execute(user_sql.decode('utf-8')).fetchone()
            result = {'strPid': res.pid, 'strUserid': str(res._id), 'strPhone': res.pMobile, 'strType': '1',
                      'strStatus': res.status,
                      'strName': res.pName,
                      'strCardNum': res.pCardNum, 'strSex': res.pSex or 'M', 'strCompanyName': res.companyName,
                      'strCommunityId': res.communityId, 'strCommunityName': res.communityName,
                      'strCompanyId': res.pcid,'strIntegral':res.integral}
            try:
                house_sql = """select * from zhsq_house2owner where pid='%s'and bz2 !='0'""" % res.pid
                house = se.execute(house_sql.decode('utf-8')).fetchall()
                house_info = []
                for h in house:
                    h_sql = """select * from zhsq_house where hid='%s'""" % h.hid
                    hs = se.execute(h_sql.decode('utf-8')).fetchone()
                    row = {}
                    row['strHouseId'] = h.hid
                    row['strHouseNum'] = h.houseNum
                    row['strBelongBuildingNum'] = hs.belongBuildingNum
                    row['strBelongBuildingUnit'] = hs.belongBuildingUnit
                    if result['strStatus'] != '1' and h.bz4 == '2': result['strStatus'] = '2'
                    house_info.append(row)
                result['arrHouseInfo'] = house_info
            except:
                result['arrHouseInfo'] = []
            try:
                com_sql = """select distinct communityId from zhsq_house2owner where pid='%s' and bz2 !='0'""" % res.pid
                com = se.execute(com_sql.decode('utf-8')).fetchall()
                row1 = {}
                for i in range(len(com)):
                    key = 'community%s' % i
                    row1[key] = com[i][0]
                result['arrCommunityInfo'] = row1
            except:
                result['arrCommunityInfo'] = ''
            se.close()
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': result}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def setuser(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        userId = reqobj.get('strId', '')
        username = reqobj.get('strName', '')
        cid = reqobj.get('strCid','')
        communityName = reqobj.get('strCommunityName','')
        userType = reqobj.get('strType', '1')
        hid = reqobj.get('strHid', '')
        pid = reqobj.get('strPid', '')
        pcid = reqobj.get('strPcid', '')
        sex = reqobj.get('strSex', 'M')
        cardNum = reqobj.get('strCardNum', 'M')
        companyName = reqobj.get('strCompanyName', '')
        img01 = reqobj.get('img01','')
        img02 = reqobj.get('img02','')
        img03 = reqobj.get('img03','')

        token = reqobj.get('strToken', '-1')

        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        se = settings.DB_SESSION()
        if userType == '0':
            update_sql = """update zhsq_employee set eName='%s', cid='%s', img01='%s', img02='%s', img03='%s', companyId='%s', companyName='%s', eSex='%s', certNo='%s' where _id='%s' """ % (username, cid, img01, img02, img03, pcid, companyName, sex, cardNum, userId)
            se.execute(update_sql.decode('utf-8'))

            # 更改房屋状态 0 已使用

        else:
            sel_sql = """select * from zhsq_house2owner where hid ='%s'and bz2 ='1'""" % hid
            houseinfo = se.execute(sel_sql.decode('utf-8')).fetchone()
            # 房屋已使用
            if houseinfo:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8901, 'rspinfo': '该房屋已被注册'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            else:
                update_sql = """update zhsq_owner set pName='%s', communityId='%s', pcid='%s',  companyName='%s', communityName='%s', pSex='%s', pCardNum='%s' where _id='%s' """ % (username, cid, pcid, companyName, communityName, sex, cardNum, userId)
                se.execute(update_sql.decode('utf-8'))

                ins_sql = """insert into zhsq_house2owner(pid,pName,communityId,hid,communityName,create_time,edit_time,img01,img02,img03,bz4,bz2,bz3) 
                                                         values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',8,1,'%s')
                                                      """ % (
                pid, username, cid, hid, communityName, datetime.datetime.now().strftime('%Y%m%d %H:%M:%S'),
                datetime.datetime.now().strftime('%Y%m%d %H:%M:%S'), img01, img02, img03, userType)
                se.execute(ins_sql.decode('utf-8'))

        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': 'erro'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def address(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': baseAddressInfo}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#获取业主名下小区id和小区名字
def getcommunity(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        pid = reqobj.get('strPid', '')
        se = settings.DB_SESSION()
        house_sql = """select distinct communityId,communityName from zhsq_house2owner where pid='%s' and bz2 !='0'""" % pid
        house = se.execute(house_sql.decode('utf-8')).fetchall()
        row = []
        content = {}
        for result in house:
            addrese_sql = """select s_province,s_city,s_area from zhsq_community where communityVettedId='%s'""" % result.communityId
            res = se.execute(addrese_sql.decode('utf-8')).fetchone()
            content = {
                'strComName': result.communityName,
                'strCommunityId': result.communityId,
                'strAddress':getarea(res.s_province,res.s_city,res.s_area).split('-')[1]
            }
            row.append(content)
            content = {}
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def community(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        se = None
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        id = reqobj.get('strId', '')
        companyId = reqobj.get('strCompany', '')
        name = reqobj.get('stName', '')
        s_province = reqobj.get('strProvince', '')
        s_city = reqobj.get('strCity', '')
        s_area = reqobj.get('strArea', '')
        token = reqobj.get('strToken', '-1')

        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        select_sql = """select * from zhsq_community where 1=1 """
        if id: select_sql += """and communityVettedId='%s' """ % id
        if companyId: select_sql += """and companyId='%s' """ % companyId
        if name: select_sql += """and name='%s' """ % name
        if s_province: select_sql += """and s_province='%s' """ % s_province
        if s_city: select_sql += """and s_city='%s' """ % s_city
        if s_area: select_sql += """and s_area='%s' """ % s_area

        se = settings.DB_SESSION()
        coms = se.execute(select_sql.decode('utf-8')).fetchall()
        result = []
        for m in coms:
            row = {}
            row['strId'], row['strName'], row['strCompanyName'], row['strCompanyId'], row['strAddress'] = m.communityVettedId, m.comName, m.companyName, m.companyId, m.communityAddress
            result.append(row)

        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': result}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        if se:se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': '请求处理异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf')

def upload( req ):
    try:
        if req.FILES:
            result = []
            for fobj in req.FILES.getlist('file'):
                userid = req.GET.get('strId', '') if req.GET.get('strId', '') else req.POST.get(
                    'strId', '') if req.POST.get('strId', '') else '0'
                type = req.GET.get('strType', '') if req.GET.get('strType', '') else req.POST.get(
                    'strType', '') if req.POST.get('strType', '') else '0'
                imgType = req.GET.get('strImgType', '') if req.GET.get('strImgType', '') else req.POST.get(
                    'strImgType', '') if req.POST.get('strImgType', '') else 'userinfo'
                subdir = os.path.join(settings.STATICPATH, 'upload', imgType, type,
                                      userid)

                if not os.access(subdir, os.R_OK):
                    os.makedirs(subdir)

                filename = fobj[ 'filename' ].decode('utf-8').encode('utf-8')
                abspath = os.path.join(subdir, filename)
                file(abspath, 'wb').write(fobj['content'])
                this_url = "http://%s/static/upload/%s/%s/%s/%s" % (req.META.get('HTTP_HOST'),imgType,type, userid, filename)
                result.append(this_url)
            content = {"rspcode": 0, "rspinfo": 'ok', "data": result}
            return HttpResponse(sjapi.jsondumps(content, ensure_ascii=False, encoding='utf-8'),
                                    content_type='application/json; charset=utf-8')
        else:
            return HttpResponse(sjapi.jsondumps({"rspcode": 0, "rspinfo": "error"}, ensure_ascii=False, encoding='utf-8'),
                                content_type='application/json; charset=utf-8')
    except Exception as e:
        content = {"rspcode": 0, "rspinfo": "error"}
        return HttpResponse(sjapi.jsondumps(content, ensure_ascii=False, encoding='utf-8'),
                                content_type='application/json; charset=utf-8')


# 门牌信息
def coordinate(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        communityId = reqobj.get('communityId', '')
        belongBuildingNum = reqobj.get('belongBuildingNum', '')
        belongBuildingUnit = reqobj.get('belongBuildingUnit', '')
        houseNum = reqobj.get('houseNum', '')

        se = settings.DB_SESSION()
        sel_sql = """select * from zhsq_house where communityId='%s'""" % communityId
        res = se.execute(sel_sql.decode('utf-8')).fetchall()
        row = {}
        for r in res:
            if row.has_key(r.belongBuildingNum):
                if row[r.belongBuildingNum].has_key(r.belongBuildingUnit):
                    if not row[r.belongBuildingNum][r.belongBuildingUnit].has_key(r.houseNum):
                        row[r.belongBuildingNum][r.belongBuildingUnit][r.houseNum] = r.hid
                else:
                    row[r.belongBuildingNum][r.belongBuildingUnit] = {}
                    row[r.belongBuildingNum][r.belongBuildingUnit][r.houseNum] = r.hid
            else:
                row[r.belongBuildingNum] = {}
                row[r.belongBuildingNum][r.belongBuildingUnit] = {}
                row[r.belongBuildingNum][r.belongBuildingUnit][r.houseNum] = r.hid


        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def coordinate_new(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        communityId = reqobj.get('communityId', '')
        belongBuildingNum = reqobj.get('belongBuildingNum', '')
        belongBuildingUnit = reqobj.get('belongBuildingUnit', '')
        houseNum = reqobj.get('houseNum', '')

        se = settings.DB_SESSION()
        sel_sql = """select distinct belongBuildingNum from zhsq_house where communityId='%s'""" % communityId
        res = se.execute(sel_sql.decode('utf-8')).fetchall()
        data=[]
        for r in res:
            data.append(r.belongBuildingNum)
        belongBuildingNum=data
        info=[]
        for i in belongBuildingNum:
            row={}
            sel_sql = """select distinct belongBuildingUnit from zhsq_house where communityId='%s' and belongBuildingNum='%s'""" % (communityId,i)
            res = se.execute(sel_sql.decode('utf-8')).fetchall()
            row['floorid']= str(i)
            ld=[]
            for unit in res:
                uni={}
                uni['unitid']=unit.belongBuildingUnit
                sel_sql = """select * from zhsq_house where communityId='%s' and belongBuildingNum='%s' and belongBuildingUnit='%s'""" % (
                communityId, i,unit.belongBuildingUnit)
                res_unit_room = se.execute(sel_sql.decode('utf-8')).fetchall()
                roo=[]
                for room in res_unit_room:
                    r={}
                    r['door']=room.houseNum
                    r['hid']=room.hid
                    roo.append(r)
                uni['vaule']=roo
                ld.append(uni)
            row['vaule']=ld
            info.append(row)

        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': info}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        print e
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

# 上报维修接口
def repairapply(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 公司id
        # pcid = reqobj.get('strPcid', '')
        #公司名称
        # pcname = reqobj.get('strCompanyName', '')
        # 小区id
        cid = reqobj.get('strCommunityId', '')
        #小区名称
        cname = reqobj.get('strComName', '')
        #报修地址
        adress = reqobj.get('strAddress', '')
        if address != '':
            detailaddress =cname+adress.split('-')[0]+'号楼'+adress.split('-')[1]+'单元'+adress.split('-')[2]
        #报修类型
        type = reqobj.get('strTypeId', '')
        #报修项目
        project = reqobj.get('strChildId', '')
        #报修原因
        reason = reqobj.get('strReason', '')
        #报修人
        pid = reqobj.get('strPid', '')
        #报修人名字
        pName = reqobj.get('strName', '')
        #报修人电话
        pMobile = reqobj.get('strPhone', '')
        #上门时间
        visittime = reqobj.get('strVisitTime','')
        img01 = reqobj.get('strImg01', '')
        img02 = reqobj.get('strImg02', '')
        img03 = reqobj.get('strImg03', '')
        token = reqobj.get('strToken', '')

        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        sel_sql = """select companyName,companyId from zhsq_community where communityVettedId='%s'""" % cid
        res = se.execute(sel_sql.decode('utf-8')).fetchone()
        if not res:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '小区输入有误'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        sql_insert = """insert into zhsq_repiarinfo(rid, pcid, pcName, cid, cName, adress, `type`, reason, pid, pName, pMobile, `status`, repair_time, bz2, img01, img02, img03, bz3,detailaddress) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s','%s')""" % (
            'RID%s' % datetime.datetime.now().strftime("%Y%m%d%H%M%S"), res.companyId, res.companyName, cid, cname, adress, type, reason,
            pid, pName, pMobile, '-1', datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), project, img01, img02, img03,visittime,detailaddress
        )
        se.execute(sql_insert.decode('utf-8'))
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#物业人员查询维修状态
def selrepairstatus(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': repairStatus}, ensure_ascii=False,encoding='utf-8'),
                content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

#添加报修顶级类型
def addrepairtop(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 顶级类别
        typename = reqobj.get('strTypeName', '')
        sql_insert = """insert into zhsq_repairtype(typename,fatherid,path) values ('%s','0','0,')""" % typename
        se.execute(sql_insert.decode('utf-8'))
        sql_select = """select id  from zhsq_repairtype where typename = '%s'""" % typename
        topid = se.execute(sql_select.decode('utf-8')).fetchone().id
        row = {}
        row['strTypeId'] = topid
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


#添加报修二级类型
def addrepairchild(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 顶级类别
        topid = reqobj.get('strTypeId')
        # 二级类别名称
        child = reqobj.get('strChildName')
        sql_select = """select *  from zhsq_repairtype where id = '%s'""" % topid
        top = se.execute(sql_select.decode('utf-8')).fetchone()
        sql_insert = """insert into zhsq_repairtype(typename, fatherid, path) values ('%s', '%s', '%s')""" % (
        child, top[0], str(top[3] + str(top[0]) + ','))
        se.execute(sql_insert.decode('utf-8'))
        sql_select = """select id  from zhsq_repairtype where typename = '%s'""" % child
        childid = se.execute(sql_select.decode('utf-8')).fetchone().id
        row = {}
        row['strChildId'] = childid
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def basetype(fatherid):
    se = settings.DB_SESSION()
    sql_select = """select *  from zhsq_repairtype where fatherid = '%s'""" % fatherid
    typeinfo = se.execute(sql_select.decode('utf-8')).fetchall()
    childinfo = []
    content = {}
    for result in typeinfo:
        content = {'strChildId': str(result.id), 'strChildName': result.typename}
        childinfo.append(content)
        content = {}
    return childinfo

# 报修基本类型
def repairbase(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        sql_select = """select *  from zhsq_repairtype where fatherid = '0' """
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchall()
        row = []
        content = {}
        for result in typeinfo:
            content = {'strTypeId': str(result.id), 'strTypeName': result.typename,'strChild':basetype(result.id)}
            row.append(content)
            content = {}
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#更新报修类型
def updaterepairtype(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        informationid = reqobj.get('strTypeId', '')
        name = reqobj.get('strTypeName', '')
        se = settings.DB_SESSION()
        sql_select = """select *  from zhsq_repairtype where id = '%s'""" % informationid
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        update_sql = """update zhsq_repairtype set typename ='%s'where id='%s'""" % (name, id)
        se.execute(update_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#删除报修类型
def delrepairtype(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        id = reqobj.get('strTypeId')
        sql_select = """select *  from zhsq_repairtype where id = '%s'""" % id
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        sql_sel = """select *  from zhsq_repairtype where path like '%{}%'""".format(id)
        res = se.execute(sql_sel.decode('utf-8')).fetchall()
        if res:
            for r in res:
                del_sql = """delete from zhsq_repairtype where id = '%s'""" % r.id
                se.execute(del_sql.decode('utf-8'))
        del_sql = """delete from zhsq_repairtype where id = '%s'""" % id
        se.execute(del_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


# 添加资讯类型
def addinformation(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 顶级类别
        typename = reqobj.get('strTypeName', '')
        sql_insert = """insert into zhsq_informationtype(typename) values ('%s')""" % (typename)
        se.execute(sql_insert.decode('utf-8'))
        sql_select = """select id  from zhsq_informationtype where typename = '%s'""" % typename
        topid = se.execute(sql_select.decode('utf-8')).fetchone().id
        row = {}
        row['strTypeId'] = topid
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def updateinformationtype(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        informationid = reqobj.get('strTypeId', '')
        name = reqobj.get('strTypeName', '')
        se = settings.DB_SESSION()
        sql_select = """select *  from zhsq_informationtype where id = '%s'""" % informationid
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        update_sql = """update zhsq_informationtype set typename ='%s'where id='%s'""" % (name, id)
        se.execute(update_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


# 获取资讯类型
def informationbase(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        sql_select = """select *  from zhsq_informationtype """
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchall()
        row = []
        content = {}
        for result in typeinfo:
            content = {'strTypeId': str(result.id), 'strTypeName': result.typename}
            row.append(content)
            content = {}
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


# 删除资讯类型
def delinformationtype(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        id = reqobj.get('strTypeId')
        sql_select = """select *  from zhsq_informationtype where id = '%s'""" % id
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        del_sql = """delete from zhsq_informationtype where id = '%s'""" % id
        se.execute(del_sql.decode('utf-8'))
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': id}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#获取状态详情
def basename(id,projectid):
    se = settings.DB_SESSION()
    top_sql = """select typename from zhsq_repairtype where id = '%s' """ % id
    toptypename = se.execute(top_sql.decode('utf-8')).fetchone().typename
    project_sql = """select typename from zhsq_repairtype where id = '%s' """ % projectid
    childtypename = se.execute(project_sql.decode('utf-8')).fetchone().typename
    se.close()
    return toptypename + '-'+ childtypename

#获取状态信息
def basestatus(id):
    for i in repairStatus:
        if i['id'] == id :
            return i['name']

#查询报修列表
def repairlist(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        cid = reqobj.get('strCommunityId', '')
        pcid =reqobj.get('strCompanyId')
        userid = reqobj.get('strUserid', '')
        usertype = reqobj.get('strType','')
        #etype:0 全部报修，1我的派单，2已完成
        etype = reqobj.get('strEtype','0')
        token = reqobj.get('strToken', '')
        # 页码
        page = int(reqobj.get('strPage', 1))
        start = (page - 1) * 10
        end = start + 10
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        row = []
        content = {}
        if usertype == '0': #物业人员 不传eid 默认为全部报修，所有都显示
            if userid == '' or etype == '0':
                select_sql = """select * from zhsq_repiarinfo where pcid = '%s' and status = '-1' order by repair_time desc """%pcid
                repairs = se.execute(select_sql.decode('utf-8')).fetchall()
                for result in repairs:
                    content = {'strRid': str(result.rid), 'strContent': result.reason,
                               'strCreatetime': result.repair_time,
                               'strTypeName': basename(result.type, result.bz2).split('-')[0],'strProject': basename(result.type, result.bz2).split('-')[1],'strStatus':result.status,'strAddress':result.adress,'strComName':result.cName}
                    row.append(content)
                    content = {}
            else:
                if etype == '1':
                    select_sql = """select distinct rid,wsid from zhsq_worksheet where eid = '%s'""" %userid
                    rids = se.execute(select_sql.decode('utf-8')).fetchall()
                    for rid in rids:
                        select_sql = """select * from zhsq_repiarinfo where rid = '%s' and status != '3' order by repair_time desc """ % rid.rid
                        repairs = se.execute(select_sql.decode('utf-8')).fetchall()
                        for result in repairs: #派单也需要更改状态
                            if result.status in ['1','2']:
                                content = {'strContent': result.reason,'strRid':rid.rid,'strWsid':rid.wsid,
                                   'strCreatetime': result.repair_time,
                                   'strTypeName': basename(result.type, result.bz2).split('-')[0],'strProject': basename(result.type, result.bz2).split('-')[1],'strStatus': result.status,'strAddress':result.adress,'strComName':result.cName}
                                row.append(content)
                                content = {}
                elif etype == '2':
                    # select_sql = """select a.wsid,a.content,b.finish_time,a.mcharge,a.scharge,b.charge,a.`status` ,b.rid from
                    #  zhsq_worksheet a left  join  zhsq_repiarinfo b   on   a.rid=b.rid where   a.eid = '%s' and b.`status`='3'""" % userid
                    # repairs = se.execute(select_sql.decode('utf-8')).fetchall()
                    # for result in repairs:
                    #     #'strStatus': basestatus(result.status)
                    #     content = {'strContent': result.content,
                    #                    'strFinshtime': str(datetime.datetime.strptime(result.finish_time, "%Y%m%d%H%M%S")),
                    #                     'strServeCharge':str(result.scharge),'strMaterialsCharge':str(result.mcharge),
                    #                     'strCharge':str(result.charge),'strStatus': result.status,'strRid':result.rid}
                    #     row.append(content)
                    #     content = {}
                    select_sql = """select distinct rid,wsid  from zhsq_worksheet where eid = '%s'""" % userid
                    rids = se.execute(select_sql.decode('utf-8')).fetchall()
                    for rid in rids:
                        select_sql = """select * from zhsq_repiarinfo where rid = '%s' and status = '3' order by repair_time desc """ % rid.rid
                        repairs = se.execute(select_sql.decode('utf-8')).fetchall()
                        for result in repairs:  # 派单也需要更改状态
                            content = {'strContent': result.reason, 'strRid': rid.rid, 'strWsid': rid.wsid,
                                       'strCreatetime':  result.repair_time,
                                       'strTypeName': basename(result.type, result.bz2).split('-')[0],
                                       'strProject': basename(result.type, result.bz2).split('-')[1],
                                       'strStatus': result.status, 'strAddress': result.adress,
                                       'strComName': result.cName}
                            row.append(content)
                            content = {}
        else:
            obj = json.loads(cid, encoding='utf-8')
            for communityid in obj:
                select_sql = """select * from zhsq_repiarinfo where cid = '%s' and pid = '%s' order by repair_time desc """ % (obj[communityid], userid)
                repairs = se.execute(select_sql.decode('utf-8')).fetchall()
                for result in repairs:
                    try:
                        strTypeName = basename(result.type, result.bz2).split('-')[0]
                        strProject = basename(result.type, result.bz2).split('-')[1]
                        # strTitle =  basename(result.type, result.bz2)
                    except:
                        strTypeName =''
                        strProject=''

                    content = {'strRid': str(result.rid), 'strContent': result.reason,
                               'strCreatetime': result.repair_time,
                               'strTypeName':  strTypeName,'strProject':strProject, 'strStatus': result.status,'strAddress':result.adress,'strComName':result.cName}
                    row.append(content)
                    content = {}
        if len(row[start:end]) < 10:
                nextpage = '0'
        else:
                nextpage = '1'
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row[start:end], 'nextpage': nextpage},
                        ensure_ascii=False, encoding='utf-8'),content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#报修评价
def repaircomment(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        wsid = reqobj.get('strWsid', '')
        rid = reqobj.get('strRid', '')
        token = reqobj.get('strToken', '')
        userid = reqobj.get('strPid','')
        startnum = reqobj.get('strStartnum','1')
        content = reqobj.get('strContent','')
        eid = reqobj.get('strEid','')
        img01 = reqobj.get('strImg01','')
        img02 = reqobj.get('strImg02','')
        img03 = reqobj.get('strImg03','')
        id = se.execute(
            """select id from zhsq_repiarcomment where rid='%s' and wsid='%s' and userid = '%s'""" % (rid, wsid,userid)).fetchone()
        if id:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '您已评价'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        user = se.execute("""select * from zhsq_owner where pid='%s'""" % userid).fetchone()
        if not user:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        select = """select * from zhsq_repiarinfo where rid='%s'""" % rid
        repair = se.execute(select.decode('utf-8')).fetchone()
        if not repair:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '上报记录不存在'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        insert_sql = """insert into zhsq_repiarcomment(rid,wsid, userid, createtime,startnum, content, img01, img02,img03,username,eid,status) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (
                rid, wsid, userid, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),startnum,content,img01,img02,img03,user.pName,eid,'1')
        se.execute(insert_sql.decode('utf-8'))
        id = se.execute("""select id from zhsq_repiarcomment where rid='%s' and wsid='%s'""" % (rid,wsid)).fetchone().id
         # 更新zhsq_repiarinfo 状态为1 已接单 状态为2 已处理 状态为-1 业主已上报 状态为3单子已完成，1评价已完成
        update_sql = """update zhsq_repiarinfo set status='3',evaluate='%s' where rid='%s'""" %(id,rid)
        se.execute(update_sql.decode('utf-8'))
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#查看评价接口
def selcomment(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        token = reqobj.get('strToken', '')
        pid = reqobj.get('strPid', '')
        eid = reqobj.get('strEid','')
        rid =reqobj.get('strRid','')
        usertype = reqobj.get('strType')
        if usertype == '0':
            try:
                comments = se.execute("""select * from zhsq_repiarcomment where  eid = '%s' and rid ='%s'""" % (eid,rid)).fetchall()
                row = []
                content = {}
                for result in comments:
                    content = {
                    'strCreateTime':result.createtime,'strStartnum': result.startnum, 'strContent': result.content,
                           'strImg01': result.img01, 'strImg02': result.img02, 'strImg03': result.img03,
                           'strUserName': result.username,'strWsid':result.wsid,'strStatus':result.status
                           }
                    row.append(content)
                    content = {}
            except:
                row = []
            se.close()
        else:
            try:
                comments = se.execute("""select * from zhsq_repiarcomment where userid = '%s' and rid = '%s'""" % (pid,rid)).fetchall()
                row = []
                content = {}
                for result in comments:
                    content = {
                    'strCreateTime': result.createtime,
                    'strStartnum': result.startnum, 'strContent': result.content,'strStatus':result.status,
                    'strImg01': result.img01, 'strImg02': result.img02, 'strImg03': result.img03,'strWsid':result.wsid
                }
                    row.append(content)
                    content = {}
            except:
                row = []
            se.close()
        return HttpResponse(
                sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo':'数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


#查看报修详情
def repairdetail(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 具体条数的id
        id = reqobj.get('strRid', '')
        se = settings.DB_SESSION()
        sql_select = """select *  from zhsq_repiarinfo where rid = '%s'""" % id
        result = se.execute(sql_select.decode('utf-8')).fetchone()
        rowinfo = []
        contents = {}
        #报修类型
        contents['strTypeName'] =basename(result.type, result.bz2).split('-')[0]
        #报修项目
        contents['strProject'] = basename(result.type, result.bz2).split('-')[1]
        #联系人
        contents['strUserName'] = result.pName
        #电话
        contents['strPhone'] = result.pMobile
        #上报时间
        contents['strRtime'] = result.repair_time
        #状态
        contents['strStatus'] = result.status
        #内容介绍
        contents['strContent'] =result.reason
        #图片
        contents['strImg01'] =result.img01
        contents['strImg02'] =result.img02
        contents['strImg03'] =result.img03
        contents['strRid'] = id
        contents['strAddress']=result.adress
        #是否评价
        sql_repaircomment = """select *  from zhsq_repiarcomment where rid = '%s'""" % id
        comment=se.execute(sql_repaircomment.decode('utf-8')).fetchone()
        if comment:
            contents['strCommentStatus'] = '1'
        else:
            contents['strCommentStatus'] ='0'
        #维修进度
        sql_repair = """select *  from zhsq_worksheet where rid = '%s'order by createtime desc """ % id
        resl = se.execute(sql_repair.decode('utf-8')).fetchall()
        row = []
        content = {}
        for res in resl:
            if res.content == None and res.scharge == None and res.mcharge ==None and res.img01 ==None:
                contents['strRepire'] = []
                contents['strWsid'] = res.wsid
            else:
                try:
                    content['strWstatus'] =basestatus(res.status)
                except:
                    content['strWstatus']=''

                content['strContent'] = res.content
                content['strServeCharge'] =res.scharge
                content['strMaterialsCharge'] =res.mcharge
                if res.fixtime:
                    content['strWtime'] = res.fixtime
                else:
                    content['strWtime']=''
                # 图片
                content['strImg01'] = res.img01
                content['strImg02'] = res.img02
                content['strImg03'] = res.img03
                row.append(content)
                content = {}
                contents['strRepire'] = row
                contents['strEid'] = res.eid
                contents['strWsid'] =res.wsid
        rowinfo.append(contents)
        se.close()
        return HttpResponse(
                sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': rowinfo}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
    except Exception as e:
        # se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo':'数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')



#抢单
def robbing(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        eid = reqobj.get('strEid', '')
        rid = reqobj.get('strRid', '')
        token = reqobj.get('strToken', '')
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        user = se.execute("""select * from zhsq_employee where eid='%s'""" % eid).fetchone()
        if not user:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')

        select = """select * from zhsq_repiarinfo where rid='%s'""" % rid
        repair = se.execute(select.decode('utf-8')).fetchone()
        if repair and repair.status == '-1':
            wsid = 'WSID%s' % datetime.datetime.now().strftime("%Y%m%d%H%M%S")
            #更新zhsq_worksheet状态为0已接单1已修好2补充方案3无法修理 sendflag  0抢单 1派单
            insert_sql = """insert into zhsq_worksheet(wsid, rid, eid, ename, ephone, status, createtime,sendflag) values('%s','%s','%s','%s','%s','%s','%s','%s')""" % (wsid, rid, user.eid, user.eName, user.mobile, '0', datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"),'0')
            se.execute(insert_sql.decode('utf-8'))
            #更新zhsq_repiarinfo 状态为1 已接单 状态为2 已处理 状态为-1 业主已上报
            update_sql = """update zhsq_repiarinfo set status='1' where rid='%s'""" % rid
            se.execute(update_sql.decode('utf-8'))
            #更新物业人员表为已接单状态 0未接单 1 已接单
            # update_sql = """update zhsq_employee set receivedflag='1' where eid='%s'""" % eid
            # se.execute(update_sql.decode('utf-8'))
            #放在消息表中 1未下发 1已接单0推送
            mailsql = "insert into zhsq_jpush ( status, titleid,title, userid, createtime, content, pushtype,usertype) values (  '1', '1', '接单', '%s', '%s', '您上报的维修项目已接单' , '0','1')" % (
            repair.pMobile, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            se.execute(mailsql.decode('utf-8'))
            mailsql = "insert into zhsq_jpush ( status, titleid,title, userid, createtime, content, pushtype,usertype) values (  '1', '3', '抢单', '%s', '%s', '恭喜您，抢单成功' , '0','0')" % (
                user.mobile, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
            se.execute(mailsql.decode('utf-8'))

        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def fix(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        #单号
        wsid = reqobj.get('strWsid', '')
        #内容
        content = reqobj.get('strContent', '')
        #服务费
        scharge = int(reqobj.get('strServeCharge', 0))
        #材料费
        mcharge = int(reqobj.get('strMaterialsCharge', 0))
        img01 = reqobj.get('strImg01', '')
        img02 = reqobj.get('strImg02', '')
        img03 = reqobj.get('strImg03', '')
        #状态
        serstatus = reqobj.get('strStatus', '')
        token = reqobj.get('strToken', '')
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        sel_sql = """select * from zhsq_worksheet where wsid = '%s'"""%wsid
        works = se.execute(sel_sql.decode('utf-8')).fetchone()
        if not works:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '输入维修单号有误'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        #更新状态2为已处理
        now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        charge = mcharge +scharge
        sel_id = """select _id from zhsq_repiarinfo where status = '2' and rid='%s'"""%works.rid
        _id =se.execute(sel_id.decode('utf-8')).fetchone()
        if _id:#sendflag 为-1，则是已有，但有新的建议
            insert_sql = """insert into zhsq_worksheet(wsid, rid, eid, ename, ephone, status,content, scharge, mcharge, fixtime, img01, img02, img03, createtime,sendflag) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (
            works.wsid, works.rid, works.eid, works.eName, works.ephone, serstatus, content, scharge, mcharge, now, img01, img02, img03, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), '-1')
            se.execute(insert_sql.decode('utf-8'))
        else:
            update_sql = """update zhsq_worksheet set status='%s', content='%s', scharge='%s', mcharge='%s', fixtime='%s', img01='%s', img02='%s', img03='%s' where wsid='%s'""" % (
            serstatus, content, scharge, mcharge, now, img01, img02, img03, wsid)
            se.execute(update_sql.decode('utf-8'))
        update_sql = """update zhsq_repiarinfo set status = '2',charge = '%s',finish_time = '%s' where rid='%s'""" % (charge, now, works.rid)
        se.execute(update_sql.decode('utf-8'))

        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        print e
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def addAddress(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 业主id
        userId = reqobj.get('strUserid', '')
        # 业主姓名
        username = reqobj.get('strName', '')
        # 手机号
        pMobile = reqobj.get('strMobile', '')
        # 小区名称
        communityName = reqobj.get('strCommunityName', '')
        # 小区id
        communityId = reqobj.get('strCommunityId', '')
        # 房屋编号
        hid = reqobj.get('strHid', '')
        # 业主id
        pid = reqobj.get('strPid', '')
        # 门牌号 几号楼 -几单元 - 门牌号
        detailaddress = reqobj.get('strHouseNum')
        belongBuildingNum = detailaddress.split('-')[0]
        belongBuildingUnit = detailaddress.split('-')[1]
        houseNum = detailaddress.split('-')[2]

        # 证明资料
        img01 = reqobj.get('img01', '')
        img02 = reqobj.get('img02', '')
        img03 = reqobj.get('img03', '')
        token = reqobj.get('strToken', '-1')
        # 业主类型
        type = reqobj.get('strType', '')
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        se = settings.DB_SESSION()
        update_token = """update api_token set createtime='%s' where tokenid='%s'""" % (now, token.tokenid)
        se.execute(update_token.decode('utf-8'))
        try:
            sel_sql ="""select * from zhsq_house where communityId ='%s' and belongBuildingNum ='%s' and belongBuildingUnit='%s' and houseNum ='%s'"""%(communityId,belongBuildingNum,belongBuildingUnit,houseNum)
            houseinfo =se.execute(sel_sql.decode('utf-8')).fetchone()
        except:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8902, 'rspinfo': '您输入的房屋地址不在本小区'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        sel_sql = """select * from zhsq_house2owner where hid ='%s' and bz2 = '1'""" % hid
        houseinfo = se.execute(sel_sql.decode('utf-8')).fetchone()
        # 房屋已使用
        if houseinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8901, 'rspinfo': '该房屋已被注册'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        else:
            ins_sql = """insert into zhsq_house2owner(pid,pName,pMobile,communityId,hid,houseNum,communityName,create_time,edit_time,img01,img02,img03,bz4,bz2) 
                                                                                 values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',8,1)
                                                                              """ % (
                pid, username, pMobile, communityId, hid, houseNum, communityName,
                datetime.datetime.now().strftime('%Y%m%d %H:%M:%S'),
                datetime.datetime.now().strftime('%Y%m%d %H:%M:%S'),
                img01, img02, img03)
            se.execute(ins_sql.decode('utf-8'))
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': 'erro'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def delAddress(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 具体地址id
        id = reqobj.get('strId', '')
        pid = reqobj.get('strPid', '')
        token = reqobj.get('strToken', '-1')
        type = reqobj.get('strType', '')
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        se = settings.DB_SESSION()
        update_token = """update api_token set createtime='%s' where tokenid='%s'""" % (now, token.tokenid)
        se.execute(update_token.decode('utf-8'))
        address = """select * from zhsq_house2owner where _id = '%s' and pid = '%s' """ % (id, pid)
        se.execute(address.decode('utf-8'))
        if not address:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': '地址异常'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        #bz4 = '-1'不让业主在页面显示
        # del_sql = """update zhsq_house2owner set bz2 = '0',bz4 = '-1' where _id = '%s' """ % id
        # se.execute(del_sql.decode('utf-8'))
        #应web端联调要求，特改为真正删除
        del_sql = """delete from zhsq_house2owner  where _id = '%s' """ % id
        se.execute(del_sql)
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': 'erro'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def getarea(province,city,area):
    for pro in baseAddressInfo:
        if pro['code'] == province:
            province = pro['name']
            for citys in pro['children']:
                if citys['code'] == city:
                    city = citys['name']
                for areas in citys['children']:
                    if areas['code'] == area:
                        area = areas['name']
    return province +'-'+ city+'-' + area
def getaddress(req):
    se = settings.DB_SESSION()
    now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        token = reqobj.get('strToken', '')
        # 业主id
        pid = reqobj.get('strPid', '')
        # 类型
        type = reqobj.get('strType', '')
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        update_token = """update api_token set createtime='%s' where tokenid='%s'""" % (now, token.tokenid)
        se.execute(update_token.decode('utf-8'))
        if type == '1':
            select_sql = """select a._id,a.pid,a.bz3,c.s_province,c.s_city,c.s_area,c.communityAddress,c.communityVettedId ,c.comName,b.belongBuildingNum,b.belongBuildingUnit,b.belongBuildingFloor,b.houseNum from
zhsq_house2owner  a left   join   zhsq_house b   on   a.hid=b.hid  
left   join   zhsq_community c  on   b.communityId = c.communityVettedId  
where   a.bz4 = '1'and a.bz2 !='0'and a.pid='%s' """ % pid
        elif type == '0':
            select_sql = """select a._id,a.pid,a.bz3,c.s_province,c.s_city,c.s_area,c.communityAddress,c.communityVettedId,c.comName,b.belongBuildingNum,b.belongBuildingUnit,b.belongBuildingFloor,b.houseNum from
            zhsq_house2owner  a left   join   zhsq_house b   on   a.hid=b.hid  
            left   join   zhsq_community c  on   b.communityId = c.communityVettedId  
            where   a.bz4 = '1'and a.bz2 !='0' and a.pid='%s' """ % pid
        res = se.execute(select_sql.decode('utf-8')).fetchall()
        row = []
        content = {}
        for result in res:

            content = {'strId': str(result._id) , 'strPid': str(result.pid),
                       'strAreas': getarea(result.s_province,result.s_city,result.s_area).split('-')[0]+getarea(result.s_province,result.s_city,result.s_area).split('-')[1]+getarea(result.s_province,result.s_city,result.s_area).split('-')[2] if result.s_province else '',
                       'strAddress': result.communityAddress if result.communityAddress else '',
                       'strName': result.comName if result.comName else '',
                       'strBuildingNum': result.belongBuildingNum if result.belongBuildingNum else '', 'strBuildingUnit': result.belongBuildingUnit if result.belongBuildingUnit else '',
                       'BuildingFloor': result.belongBuildingFloor if result.belongBuildingFloor else '',
                       'strhouseNum': result.houseNum if result.houseNum else '','strCommunityId':result.communityVettedId if result.communityVettedId else ''}
            row.append(content)
            content = {}
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#获取咨询信息
def getinformation(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # if req.method == "POST":
        #     req_obj_dict = eval(dict(req.POST).keys()[0])
        # reqobj = req_obj_dict
        # print req.body
        # print type(reqobj)
        # 获取请求要素
        token = reqobj.get('strToken', '-1')
        # 咨迅类型  1小区公告 2 便民服务 3 周围咨询
        informationtype = reqobj.get('strTypeId')
        # 业主类型
        type = reqobj.get('strType', '')
        # 小区id
        cid = reqobj.get('strCommunityId','')
        if cid == '':
            cid = '1'
        else:
            try:
                obj=json.loads(cid, encoding='utf-8')
            except:
                obj = cid
        # 一页中的最后id
        # id = int(reqobj.get('strPaginationId', 1))
        page = int(reqobj.get('strPage',1))
        start = (page-1)*10
        end = start+10
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        se = settings.DB_SESSION()
        update_token = """update api_token set createtime='%s' where tokenid='%s'""" % (now, token.tokenid)
        se.execute(update_token.decode('utf-8'))
        info = []
        if cid == '1':
                sel_sql = """select messageNo,_id,infoType,eMessageObject,cid,title,bz1,sendtime,bz3 from zhsq_information where infoType = '%s' and status = '01'  order by _id desc """ %informationtype
            # sel_sql +=""" offset {}""".format(start)
                informations = se.execute(sel_sql.decode('utf-8')).fetchall()
                infotype_sql = """select * from zhsq_informationtype where id ='%s'""" % informationtype
                typename = se.execute(infotype_sql.decode('utf-8')).fetchone().typename
                content = {}
                row = []
                for information in informations:
            # 0 物业人员能看，1业主能看，2所有人都能看
                    if information.eMessageObject == '0':
                        if type == '0':
                            content = {'strId': information._id,
                               'strTypeId': information.infoType, 'strTypeName': typename,
                               'strMessageObject': information.eMessageObject,
                               'strCommunityId': information.cid,
                               'strTitle': information.title,
                               'strCreateTime': information.sendtime if information.sendtime else '', 'strStick': information.bz3 if information.bz3 else '',
                                   'strTitleImg':information.bz1}
                            row.append(content)
                            content = {}
                    elif information.eMessageObject == '1':
                        if type == '1':
                            content = {'strId': information._id,
                                   'strTypeId': information.infoType, 'strTypeName': typename,
                                   'strMessageObject': information.eMessageObject,
                                   'strCommunityId': information.cid,
                                   'strTitle': information.title,
                                   'strCreateTime':information.sendtime if information.sendtime else '', 'strStick': information.bz3 if information.bz3 else '',
                                   'strTitleImg': information.bz1
                                   }
                            row.append(content)
                            content = {}
                    elif information.eMessageObject == '2':
                        if information.bz1:
                            strTitleImg=information.bz1
                        else:
                            strTitleImg = ''
                        content = {'strId': information._id,
                               'strTypeId': information.infoType, 'strTypeName': typename,
                               'strMessageObject': information.eMessageObject,
                               'strCommunityId': information.cid,
                               'strTitle': information.title,
                               'strCreateTime': information.sendtime if information.sendtime else '', 'strStick': information.bz3 if information.bz3 else '','strTitleImg':strTitleImg  }
                        row.append(content)
                        content = {}
                info.extend(row)

        else:
            for communityid in obj:
                sel_sql = """select messageNo,_id,infoType,eMessageObject,cid,title,bz1,sendtime,bz3 from zhsq_information where infoType = '%s' and status = '01' and cid='%s' order by _id desc """ %(informationtype, obj[communityid])
            # sel_sql +=""" offset {}""".format(start)
                informations = se.execute(sel_sql.decode('utf-8')).fetchall()
                community_sql = """select * from zhsq_community where communityVettedId ='%s'""" % obj[communityid]
                communitys = se.execute(community_sql.decode('utf-8')).fetchone()
                infotype_sql = """select * from zhsq_informationtype where id ='%s'""" % informationtype
                typename = se.execute(infotype_sql.decode('utf-8')).fetchone().typename
                content = {}
                row = []
                for information in informations:
            # 0 物业人员能看，1业主能看，2所有人都能看
                    if information.eMessageObject == '0':
                        if type == '0':
                            content = {'strId': information._id,
                               'strTypeId': information.infoType, 'strTypeName': typename,
                               'strMessageObject': information.eMessageObject,
                               'strCommunityId': information.cid, 'strComName': communitys.comName,
                               'strPcName': communitys.companyName, 'strTitle': information.title,
                               'strCreateTime': information.sendtime if information.sendtime else '' if information.sendtime else '', 'strStick': information.bz3 if information.bz3 else '',
                                   'strTitleImg':information.bz1}
                            row.append(content)
                            content = {}
                    elif information.eMessageObject == '1':
                        if type == '1':
                            content = {'strId': information._id,
                                   'strTypeId': information.infoType, 'strTypeName': typename,
                                   'strMessageObject': information.eMessageObject,
                                   'strCommunityId': information.cid, 'strComName': communitys.comName,
                                   'strPcName': communitys.companyName, 'strTitle': information.title,
                                   'strCreateTime': information.sendtime if information.sendtime else '', 'strStick': information.bz3 if information.bz3 else '',
                                   'strTitleImg': information.bz1
                                   }
                            row.append(content)
                            content = {}
                    elif information.eMessageObject == '2':
                        if information.bz1:
                            strTitleImg=information.bz1
                        else:
                            strTitleImg = ''
                        content = {'strId': information._id,
                               'strTypeId': information.infoType, 'strTypeName': typename,
                               'strMessageObject': information.eMessageObject,
                               'strCommunityId': information.cid, 'strComName': communitys.comName,
                               'strPcName': communitys.companyName, 'strTitle': information.title,
                               'strCreateTime': information.sendtime if information.sendtime else '', 'strStick': information.bz3 if information.bz3 else '','strTitleImg':strTitleImg  }
                        row.append(content)
                        content = {}
                info.extend(row)
        if len(info[start:end]) < 10:
            nextpage = '0'
        else:
            nextpage = '1'
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': info[start:end],'nextpage':nextpage}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def informationdetail(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        token = reqobj.get('strToken', '-1')
        # 具体条数的id
        id = int(reqobj.get('strId', 1))
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        se = settings.DB_SESSION()
        update_token = """update api_token set createtime='%s' where tokenid='%s'""" % (now, token.tokenid)
        se.execute(update_token.decode('utf-8'))
        sel_sql = """select * from zhsq_information where _id = '%s'""" % (id)
        information = se.execute(sel_sql.decode('utf-8')).fetchone()
        community_sql = """select * from zhsq_community where communityVettedId ='%s'""" % information.cid
        communitys = se.execute(community_sql.decode('utf-8')).fetchone()
        infotype_sql = """select * from zhsq_informationtype where id ='%s'""" % information.infoType
        typename = se.execute(infotype_sql.decode('utf-8')).fetchone().typename
        row = {}
        row['strMessageNo'] = information.messageNo
        row['strTypeId'] = information.infoType
        row['strTypeName'] = typename
        row['strPcName'] = communitys.companyName
        row['strMessageObject'] = information.eMessageObject
        row['strComName'] = communitys.comName
        row['strTitle'] = information.title
        row['strStatus'] = information.status
        row['strComtent'] = information.nr
        row['strImgUrl'] = information.bz1
        if information.sendtime:
            strtime=information.sendtime
        else:
            strtime=''
        row['strCreateTime'] = strtime
        row['strStick'] = information.bz3
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': 'erro'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


# 资讯置顶列表
def informationstick(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        # if req.method == "POST":
        #     req_obj_dict = eval(dict(req.POST).keys()[0])
        # reqobj = req_obj_dict
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        token = reqobj.get('strToken', '-1')
        # 咨迅类型  1小区公告 2 便民服务 3 周围咨询
        informationtype = reqobj.get('strTypeId','')
        # 业主类型
        type = reqobj.get('strType', '')
        # 小区id
        cid = reqobj.get('strCommunityId','')
        if cid == '' :
            cid = '1'
        else:
            try:
                obj=json.loads(cid, encoding='utf-8')
            except:
                obj = cid

        #页数
        page = int(reqobj.get('strPage',1))
        start = (page-1)*10
        end = start+10
        flag, token = check_token_valid(token)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8003, 'rspinfo': token}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        now = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
        se = settings.DB_SESSION()
        update_token = """update api_token set createtime='%s' where tokenid='%s'""" % (now, token.tokenid)
        se.execute(update_token.decode('utf-8'))
        info = []
        if cid == '1':
            if informationtype == '':
                sel_sql = """select messageNo,_id,infoType,eMessageObject,cid,title,bz2,bz3,bz1,sendtime from zhsq_information where  status = '01' and bz3 = '0' order by _id desc """
            else:
                sel_sql = """select messageNo,_id,infoType,eMessageObject,cid,title,bz2,bz3,bz1,sendtime from zhsq_information where infoType = '%s' and  status = '01' and bz3 = '0' order by _id desc """% informationtype
            informations = se.execute(sel_sql.decode('utf-8')).fetchall()
            try:
                infotype_sql = """select * from zhsq_informationtype where id ='%s'""" % informationtype
                typename = se.execute(infotype_sql.decode('utf-8')).fetchone().typename
            except:
                typename = ""
            content = {}
            row = []
            for information in informations:
                community_sql = """select * from zhsq_community where communityVettedId ='%s'""" % information.cid
                communitys = se.execute(community_sql.decode('utf-8')).fetchone()
                # 0 物业人员能看，1业主能看，2所有人都能看
                if information.eMessageObject == '0':
                    if type == '0':
                        content = { 'strId': str(information._id),
                                   'strTypeId': information.infoType, 'strTypeName': typename,
                                   'strMessageObject': information.eMessageObject,
                                   'strCommunityId': information.cid, 'strComName': communitys.comName,
                                   'strPcName': communitys.companyName, 'strTitle': information.title,
                                   'strCreateTime': information.sendtime  if information.sendtime else '', 'strStick': information.bz3,'strTitleImg':information.bz1}
                        row.append(content)
                        content = {}

                elif information.eMessageObject == '1':
                    if type == '1':
                        content = {'strId': str(information._id),
                                   'strTypeId': information.infoType, 'strTypeName': typename,
                                   'strMessageObject': information.eMessageObject,
                                   'strCommunityId': information.cid, 'strComName': communitys.comName,
                                   'strPcName': communitys.companyName, 'strTitle': information.title,
                                   'strCreateTime': information.sendtime  if information.sendtime else '', 'strStick': information.bz3,'strTitleImg':information.bz1}
                        row.append(content)
                        content = {}
                elif information.eMessageObject == '2':
                    content = {'strId': str(information._id),
                               'strTypeId': information.infoType, 'strTypeName': typename,
                               'strMessageObject': information.eMessageObject,
                               'strCommunityId': information.cid, 'strComName': communitys.comName,
                               'strPcName': communitys.companyName, 'strTitle': information.title,
                               'strCreateTime': information.sendtime  if information.sendtime else '', 'strStick': information.bz3,'strTitleImg':information.bz1}
                    row.append(content)
                    content = {}
            info.extend(row)
        else:
            for communityid in obj:
                if informationtype == '':
                    sel_sql = """select messageNo,_id,infoType,eMessageObject,cid,title,bz2,bz3 ,bz1,sendtime from zhsq_information where status = '01' and cid='%s' and bz3 = '0' order by _id desc """ % obj[communityid]
                else:
                    sel_sql = """select messageNo,_id,infoType,eMessageObject,cid,title,bz2,bz3 ,bz1,sendtime from zhsq_information where infoType = '%s' and status = '01' and cid='%s' and bz3 = '0' order by _id desc """ % (
                        informationtype, obj[communityid])
                # sel_sql +=""" offset {}""".format(start)
                informations = se.execute(sel_sql.decode('utf-8')).fetchall()
                community_sql = """select * from zhsq_community where communityVettedId ='%s'""" % obj[communityid]
                communitys = se.execute(community_sql.decode('utf-8')).fetchone()
                try:
                    infotype_sql = """select * from zhsq_informationtype where id ='%s'""" % informationtype
                    typename = se.execute(infotype_sql.decode('utf-8')).fetchone().typename
                except:
                    typename = ""
                content = {}
                row = []
                for information in informations:
                    # 0 物业人员能看，1业主能看，2所有人都能看
                    if information.eMessageObject == '0':
                        if type == '0':
                            content = {'strId': str(information._id),
                                       'strTypeId': information.infoType, 'strTypeName': typename,
                                       'strMessageObject': information.eMessageObject,
                                       'strCommunityId': information.cid, 'strComName': communitys.comName,
                                       'strPcName': communitys.companyName, 'strTitle': information.title,
                                       'strCreateTime': information.sendtime  if information.sendtime else '', 'strStick': information.bz3,'strTitleImg':information.bz1}
                            row.append(content)
                            content = {}
                    elif information.eMessageObject == '1':
                        if type == '1':
                            content = {'strId': str(information._id),
                                       'strTypeId': information.infoType, 'strTypeName': typename,
                                       'strMessageObject': information.eMessageObject,
                                       'strCommunityId': information.cid, 'strComName': communitys.comName,
                                       'strPcName': communitys.companyName, 'strTitle': information.title,
                                       'strCreateTime': information.sendtime  if information.sendtime else '', 'strStick': information.bz3,'strTitleImg':information.bz1}
                            row.append(content)
                            content = {}
                    elif information.eMessageObject == '2':
                        content = {'strId': str(information._id),
                                   'strTypeId': information.infoType, 'strTypeName': typename,
                                   'strMessageObject': information.eMessageObject,
                                   'strCommunityId': information.cid, 'strComName': communitys.comName,
                                   'strPcName': communitys.companyName, 'strTitle': information.title,
                                   'strCreateTime': information.sendtime  if information.sendtime else '' , 'strStick': information.bz3,'strTitleImg':information.bz1}
                        row.append(content)
                        content = {}
                info.extend(row)
        if len(info[start:end]) < 10:
            nextpage = '0'
        else:
            nextpage = '1'
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': info[start:end],'nextpage':nextpage}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


# 增加banner
def addbanner(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # banner标题
        title = reqobj.get('strTitle', '')
        # img
        img = reqobj.get('strImg', '')
        # 展示时间之开始时间
        starttime = reqobj.get('strStartTime', '')
        # if starttime:starttime.strftime("%Y%m%d%H%M%S")
        # else: starttime=''
        # 结束时间
        endtime = reqobj.get('strEndTime', '')
        # if endtime:endtime.strftime("%Y%m%d%H%M%S")
        # else: endtime=''
        # 上下架状态0上架1下架
        status = reqobj.get('strStatus', '')
        # 跳转类型
        type = reqobj.get('strType', '')
        # url 跳转连接
        url = reqobj.get('strUrl', '')
        # banner位置 0顶部 1底部
        topflag = reqobj.get('strTopFlag', '')
        #物业公司id
        pcid = reqobj.get('strPcid', '-1')
        sql_insert = """insert into zhsq_banner(title, img, starttime, endtime, status, createtime, `type`, url, topflag, pcid) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s')""" % (
            title, img, starttime, endtime, status, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), type, url,
            topflag,pcid)
        se.execute(sql_insert.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


# 删除banner
def delbanner(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        id = reqobj.get('strId')
        sql_select = """select *  from zhsq_banner where id = '%s'""" % id
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        del_sql = """delete from zhsq_banner where id = '%s'""" % id
        se.execute(del_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': id}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


# 修改banner
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
def updatebanner(req):
    try:
        se = settings.DB_SESSION()
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        bannerid = reqobj.get('strId', '')
        sel_sql = """select * from zhsq_banner where id = '%s'"""%bannerid
        bannerinfo = se.execute(sel_sql.decode('utf-8')).fetchone()
        # banner标题
        title = reqobj.get('strTitle', '')
        if title == '':
            title = bannerinfo.title
        # img
        img = reqobj.get('strImg', '')
        if img == '':
            img = bannerinfo.img
        # 展示时间之开始时间
        starttime = reqobj.get('strStartTime', '')
        if starttime == '':
            starttime = bannerinfo.starttime
        # if starttime:starttime.strftime("%Y%m%d%H%M%S")
        # else: starttime=''
        # 结束时间
        endtime = reqobj.get('strEndTime', '')
        if endtime == '':
            endtime = bannerinfo.endtime
        # if endtime:endtime.strftime("%Y%m%d%H%M%S")
        # else: endtime=''
        # 上下架状态0上架1下架
        status = reqobj.get('strStatus', '')
        if status == '':
            status = bannerinfo.status
        # 跳转类型
        type = reqobj.get('strType', '')
        if type == '':
            type = bannerinfo.type
        # url 跳转连接
        url = reqobj.get('strUrl', '')
        if url == '':
            url = bannerinfo.url
        # banner位置 0顶部 1底部
        topflag = reqobj.get('strTopFlag', '')
        if topflag == '':
            topflag = bannerinfo.topflag
        pcid = reqobj.get('strPcid', '-1')
        if pcid == '-1':
            pcid = bannerinfo.pcid
        update_sql = """update zhsq_banner set title ='%s', img='%s', starttime='%s', endtime='%s', status='%s', createtime='%s', `type`='%s', url='%s', topflag ='%s',pcid = '%s'where id='%s'""" % (
            title,img, starttime, endtime, status, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), type, url,
            topflag, pcid, bannerid)
        se.execute(update_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

# 查询banner
def selbanner(req):
    try:
        se = settings.DB_SESSION()
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        pcid = reqobj.get('strPcid', '-1')
        if pcid == '-1':
            sel_sql = """select * from zhsq_banner where status = '0' order by topflag"""
            pcname = ''
        else:
            sel_pcname = """select companyName from zhsq_estate where pcid = '%s'"""%pcid
            pcname = se.execute(sel_pcname.decode('utf-8')).fetchone().companyName
            sel_sql = """select * from zhsq_banner where status = '0'and pcid = '%s' order by topflag"""%pcid
        banners = se.execute(sel_sql.decode('utf-8')).fetchall()
        content = {}
        row = []
        for banner in banners:
            content = {'strId': banner.id, 'strTitle': banner.title if banner.title else '',
                       'str_Img': banner.img, 'strStartTime': banner.starttime,
                       'strEndTime': banner.endtime, 'strStatus': banner.status, 'strCreateTime': banner.createtime if banner.createtime else '',
                       'strType': banner.type if banner.type else '', 'strUrl': banner.url if banner.url else '','strPcname':pcname,
                       'strTopFlag': banner.topflag,'strPcid':banner.pcid if banner.pcid else ''}
            row.append(content)
            content = {}
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#增加表扬类型
def addcomplainpraisetype(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 表扬投诉类别
        typename = reqobj.get('strTypeName', '')
        sql_insert = """insert into zhsq_complainpraisetype(typename) values ('%s')""" % (typename)
        se.execute(sql_insert.decode('utf-8'))
        sql_select = """select id  from zhsq_complainpraisetype where typename = '%s'""" % typename
        typeid = se.execute(sql_select.decode('utf-8')).fetchone().id
        row = {}
        row['strTypeId'] = typeid
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def updatecomplainpraisetype(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        informationid = reqobj.get('strTypeId', '')
        name = reqobj.get('strTypeName', '')
        se = settings.DB_SESSION()
        sql_select = """select *  from zhsq_complainpraisetype where id = '%s'""" % informationid
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        update_sql = """update zhsq_complainpraisetype set typename ='%s'where id='%s'""" % (name, id)
        se.execute(update_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def delcomplainpraisetype(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        id = reqobj.get('strTypeId')
        sql_select = """select *  from zhsq_complainpraisetype where id = '%s'""" % id
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        del_sql = """delete from zhsq_complainpraisetype where id = '%s'""" % id
        se.execute(del_sql.decode('utf-8'))
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': id}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def complainpraisebase(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        sql_select = """select *  from zhsq_complainpraisetype """
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchall()
        row = []
        content = {}
        for result in typeinfo:
            content = {'strTypeId': str(result.id), 'strTypename': result.typename}
            row.append(content)
            content = {}
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#增加表扬
def addcomplainpraise(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素 0未批复 1 已批复 -1 已删除
        # 投诉表扬标题
        #title = reqobj.get('strTitle', '')
        # 投诉表扬内容
        content = reqobj.get('strContent')
        # img
        img01 = reqobj.get('strImg01', '')
        img02 = reqobj.get('strImg02', '')
        img03 = reqobj.get('strImg03', '')
        # 类型
        type = reqobj.get('strTypeId', '')
        # userid
        pid = reqobj.get('strPid', '')
        # 小区id
        cid = reqobj.get('strCommunityId')
        sel_companyId = """select companyId from zhsq_community where communityVettedId ='%s'""" % cid
        companyId = se.execute(sel_companyId.decode('utf-8')).fetchone().companyId
        sql_insert = """insert into zhsq_complainpraise( content, img01, userid, createtime, status, fatherid, path,`type`,cid,img02,img03,companyId) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s','%s')""" % (
             content, img01, pid, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), '0', '0', '0,', type, cid,img02,img03,companyId)
        se.execute(sql_insert.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def approvecomplainpraise(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 反馈id
        id = reqobj.get('strId')
        sql_select = """select *  from zhsq_complainpraise where id = '%s'""" % id
        contents = se.execute(sql_select.decode('utf-8')).fetchone()
        # 二级类别名称
        cid1 = contents.cid
        title = ''
        content = reqobj.get('strContent','')
        img = reqobj.get('strImg', '')
        if img == '':
            img=contents.img
        cid = reqobj.get('strCommunityId')
        type = contents.type
        # userid
        eid = reqobj.get('strEid')
        # 类型
        usertype = reqobj.get('strUserType')
        if usertype == '0' and cid1 == cid:  # 物业人员
            sql_insert = """insert into zhsq_complainpraise(title, content, img, userid, createtime, status, fatherid, path,`type`,cid) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s','%s')""" % (
                title, content, img, eid, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), '1', contents.id,
                str(contents.path + str(contents.id) + ','), type, cid)
            se.execute(sql_insert.decode('utf-8'))
            update_sql = """update zhsq_complainpraise set status ='1'where id='%s'""" % (contents.id)
            se.execute(update_sql.decode('utf-8'))
        # elif usertype == '1' and cid == cid1:
        #     sql_insert = """insert into zhsq_complainpraise(title, content, img, userid, createtime, status, fatherid, path,`type`,cid) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s','%s')""" % (
        #         title, content, img, eid, datetime.datetime.now().strftime("%Y%m%d%H%M%S"), '1', contents.id,
        #         str(contents.path + str(contents.id) + ','), type, cid)
        #     se.execute(sql_insert.decode('utf-8'))
        else:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '您无操作权限'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def delcomplainpraise(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 删除批评表扬的id
        id = reqobj.get('strId')
        usertype = reqobj.get('strUserType', '0')
        # userid = reqobj.get('strUserId')
        # cid = reqobj.get('strCommunityId')
        sql_select = """select *  from zhsq_complainpraise where id = '%s'""" % id
        typeinfo = se.execute(sql_select.decode('utf-8')).fetchone()
        if not typeinfo:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        id = typeinfo.id
        sql_sel = """select *  from zhsq_complainpraise where path like '%{}%'""".format(id)
        res = se.execute(sql_sel.decode('utf-8')).fetchall()
        # and cid == typeinfo.cid
        if usertype == '0' :
            if res:
                for r in res:
                    update_sql = """update zhsq_complainpraise set status ='-1'where id='%s'""" % (r.id)
                    se.execute(update_sql.decode('utf-8'))
            update_sql = """update zhsq_complainpraise set status ='-1'where id='%s'""" % (id)
            se.execute(update_sql.decode('utf-8'))
        # else:
        #     if userid == typeinfo.userid and typeinfo.status != '1':
        #         if res:
        #             for r in res:
        #                 update_sql = """update zhsq_complainpraise set status ='-1'where id='%s'""" % (r.id)
        #                 se.execute(update_sql.decode('utf-8'))
        #         update_sql = """update zhsq_complainpraise set status ='-1'where id='%s'""" % (id)
        #         se.execute(update_sql.decode('utf-8'))
        #     else:
        #         return HttpResponse(
        #             sjapi.jsondumps({'rspcode': 8003, 'rspinfo': '您没有权限操作'}, ensure_ascii=False, encoding='utf-8'),
        #             content_type='application/json; charset=utf-8')
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def basecomment(fatherid):
    se = settings.DB_SESSION()
    sql_select = """select *  from zhsq_complainpraise where fatherid = '%s'""" % fatherid
    typeinfo = se.execute(sql_select.decode('utf-8')).fetchall()
    childinfo = []
    content = {}
    for result in typeinfo:
        try:
            sql_select = """select pName  from zhsq_owner where pid = '%s' """ % result.userid
            pName = se.execute(sql_select.decode('utf-8')).fetchone()[0]
        except:
            sql_select = """select eName  from zhsq_employee where eid = '%s' """ % result.userid
            pName = se.execute(sql_select.decode('utf-8')).fetchone()[0]
        sql_type = """select typename  from zhsq_complainpraisetype where id = '%s' """ % result.type
        typename = se.execute(sql_type.decode('utf-8')).fetchone().typename
        content = {'strId': str(result.id), 'strContent': result.content,
                   'strImg': result.img,
                   'strUserName': pName, 'strCreatetime': result.createtime,
                   'strType': typename }
        childinfo.append(content)
        content = {}
    se.close()
    return childinfo


def selcomplainpraise(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        #userid
        userid = reqobj.get('strUserid', '')
        #类型
        usertype = reqobj.get('strType','')
        #页码
        page = int(reqobj.get('strPage', 1))
        start = (page - 1) * 10
        end = start + 10
        se = settings.DB_SESSION()
        row = []
        content = {}
        if usertype == '0':
            sel_pcid = """select companyId from zhsq_employee where eid ='%s'"""%userid
            companyId = se.execute(sel_pcid.decode('utf-8')).fetchone().companyId
            #0未批复 1 已批复 -1 已删除
            sel_sql = """select * from zhsq_complainpraise  where  fatherid = '0' and  status != '-1' and companyId = '%s' order by createtime desc""" % companyId
            informations = se.execute(sel_sql.decode('utf-8')).fetchall()
            for result in informations:
                community_sql = """select comName from zhsq_community where communityVettedId ='%s'""" % result.cid
                comName = se.execute(community_sql.decode('utf-8')).fetchone().comName
                sql_select = """select pName  from zhsq_owner where pid = '%s' """ % result.userid
                pName = se.execute(sql_select.decode('utf-8')).fetchone().pName
                if not pName:
                    sql_select = """select eName  from zhsq_employee where eid = '%s' """ % result.userid
                    pName = se.execute(sql_select.decode('utf-8')).fetchone().eName
                sql_type = """select typename  from zhsq_complainpraisetype where id = '%s' """ % result.type
                typename = se.execute(sql_type.decode('utf-8')).fetchone().typename
                content = {'strId': str(result.id),  'strContent': result.content,
                       'strUserName': pName,
                       'strCreatetime': result.createtime,
                       'strType': typename, 'strComName': comName}
                row.append(content)
                content = {}
        else:
            sel_sql = """select * from zhsq_complainpraise  where  fatherid = '0' and  status != '-1' and userid = '%s' order by id desc """ % userid
            informations = se.execute(sel_sql.decode('utf-8')).fetchall()
            for result in informations:
                community_sql = """select comName from zhsq_community where communityVettedId ='%s'""" % result.cid
                comName = se.execute(community_sql.decode('utf-8')).fetchone().comName
                sql_select = """select pName  from zhsq_owner where pid = '%s' """ % result.userid
                pName = se.execute(sql_select.decode('utf-8')).fetchone().pName
                if not pName:
                    sql_select = """select eName  from zhsq_employee where eid = '%s' """ % result.userid
                    pName = se.execute(sql_select.decode('utf-8')).fetchone().eName
                sql_type = """select typename  from zhsq_complainpraisetype where id = '%s' """ % result.type
                typename = se.execute(sql_type.decode('utf-8')).fetchone().typename
                content = {'strId': str(result.id), 'strContent': result.content,
                           'strUserName': pName,
                           'strCreatetime': result.createtime,
                           'strType': typename, 'strComName': comName}
                row.append(content)
                content = {}
        if len(row[start:end]) < 10:
            nextpage = '0'
        else:
            nextpage = '1'
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row[start:end],'nextpage':nextpage}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def complainpraisedetail(req):
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 具体条数的id
        id = int(reqobj.get('strId', 1))
        se = settings.DB_SESSION()
        sql_select = """select *  from zhsq_complainpraise where fatherid = '0' and id = '%s' order by concat(path,id)""" % id
        result = se.execute(sql_select.decode('utf-8')).fetchone()
        community_sql = """select comName from zhsq_community where communityVettedId ='%s'""" % result.cid
        comName = se.execute(community_sql.decode('utf-8')).fetchone().comName
        row = []
        content = {}
        try:
            sql_select = """select pName  from zhsq_owner where pid = '%s' """ % result.userid
            pName = se.execute(sql_select.decode('utf-8')).fetchone()[0]
        except:
            sql_select = """select eName  from zhsq_employee where eid = '%s' """ % result.userid
            pName = se.execute(sql_select.decode('utf-8')).fetchone()[0]
        sql_type = """select typename  from zhsq_complainpraisetype where id = '%s' """ % result.type
        typename = se.execute(sql_type.decode('utf-8')).fetchone().typename
        content = {'strId': str(result.id), 'strContent': result.content,
                       'strImg01': result.img01,
                        'strImg02': result.img02,'strImg03': result.img03,
                       'strUserName': pName,
                       'strCreatetime': result.createtime,
                       'strType': typename, 'strComName': comName,
                       'strComment': basecomment(result.id)}
        row.append(content)
        content = {}
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#增加快递接口
def addexpress(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        # 快递编号
        trackid = reqobj.get('strTrackId', '')
        # 快递类型 如中通快递、百世快递
        expresstype = reqobj.get('strExpressType', '')
        # 发件人
        fromuser = reqobj.get('strFromUser', '')
        #收件人
        touser = reqobj.get('strToUser', '')
        #收件人地址
        #小区id
        cid = reqobj.get('strCommunityId')
        #楼栋号
        belongBuildingNum = reqobj.get('belongBuildingNum', '')
        #单元号
        belongBuildingUnit = reqobj.get('belongBuildingUnit', '')
        #门牌号
        houseNum = reqobj.get('houseNum', '')
        address = cid +'-'+ belongBuildingNum + '-' + belongBuildingUnit + '-' + houseNum
        try:
            hid_sql = """select hid from zhsq_house where communityId ='%s' and belongBuildingNum = '%s' and belongBuildingUnit ='%s' and houseNum = '%s'"""%(cid, belongBuildingNum,belongBuildingUnit,houseNum)
            hid = se.execute(hid_sql.decode('utf-8')).fetchone().hid
            user_sql = """select * from zhsq_house2owner where hid ='%s' """ % hid
            pid = se.execute(user_sql.decode('utf-8')).fetchone()
        except:
            pid = ''
        #收件人电话
        phone = reqobj.get('strPhone', '')
        sql_insert = """insert into zhsq_express(trackid, expresstype, fromuser, touser, address,phone, createtime, status,userid) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s','%s')""" % (
            trackid, expresstype, fromuser, touser, address,phone, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), '1',pid.pid)
        se.execute(sql_insert.decode('utf-8'))
        # 放在消息表中 1未下发 1已接单0推送 2快递
        mailsql = "insert into zhsq_jpush ( status, titleid,title, userid, createtime, content, pushtype) values (  '1', '2', '快递', '%s', '%s', '您有新快递' , '0')" % (
            pid.pMobile, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        se.execute(mailsql.decode('utf-8'))

        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '数据请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

#查询快递接口
def selexpress(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        userid = reqobj.get('strUserid', '')
        # 页码
        page = int(reqobj.get('strPage', 1))
        start = (page - 1) * 10
        end = start + 10
        se = settings.DB_SESSION()
        sel_sql = """select * from zhsq_express  where  userid = '%s' order by createtime desc """ % userid
        informations = se.execute(sel_sql.decode('utf-8')).fetchall()
        row = []
        content = {}
        for result in informations:
            content = {'strTrackId': str(result.trackid), 'strExpressType': result.expresstype,
                       'strCreatetime': result.createtime,
                       }
            row.append(content)
            content = {}
        if len(row[start:end]) < 10:
            nextpage = '0'
        else:
            nextpage = '1'
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row[start:end], 'nextpage': nextpage},
                            ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def selmessage(req):
    se = settings.DB_SESSION()
    try:
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        userid = reqobj.get('strPhone', '')
        type = reqobj.get('strType','1')
        # 页码
        page = int(reqobj.get('strPage', 1))
        start = (page - 1) * 10
        end = start + 10
        se = settings.DB_SESSION()
        sel_sql = """select * from zhsq_jpush  where  userid = '%s' and usertype = '%s'  order by createtime desc """ % (userid,type)
        informations = se.execute(sel_sql.decode('utf-8')).fetchall()
        row = []
        content = {}
        for result in informations:
            content = {'strContent': result.content,
                       'strCreatetime':result.createtime,
                       }
            row.append(content)
            content = {}
        if len(row[start:end]) < 10:
            nextpage = '0'
        else:
            nextpage = '1'
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok', 'data': row[start:end], 'nextpage': nextpage},
                            ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8004, 'rspinfo': '服务器请求异常'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')

def updatepwd(req):
    try:
        se = settings.DB_SESSION()
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        newpwd =reqobj.get('strNewpwd','')
        oldpwd = reqobj.get('strOldpwd','')
        userid = reqobj.get('strUserid','')
        usertype = reqobj.get('strType','')
        oldpwd1 = get_md5(oldpwd)
        passwd = get_md5(newpwd)
        if usertype == '0':
            sel_sql = """select * from zhsq_employee where eid = '%s'""" % userid
            user = se.execute(sel_sql.decode('utf-8')).fetchone()
            if not user:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '该用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            if user.password != oldpwd1:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '用户名或密码错误'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')

            update_sql = """update zhsq_employee set password='%s' where eid='%s' """ % (passwd,userid)
            se.execute(update_sql.decode('utf-8'))
        else:
            sel_sql = """select * from zhsq_owner where pid = '%s'""" % userid
            user = se.execute(sel_sql.decode('utf-8')).fetchone()
            if not user:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '该用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            if user.password != oldpwd1:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '用户名或密码错误'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            update_sql = """update zhsq_owner set password='%s' where pid='%s' """ % (passwd, userid)
            se.execute(update_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def offuser(req):
    try:
        se = settings.DB_SESSION()
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        userid = reqobj.get('strUserid','')
        usertype = reqobj.get('strType','')
        if usertype == '0':
            sel_sql = """select * from zhsq_employee where eid = '%s'""" % userid
            user = se.execute(sel_sql.decode('utf-8')).fetchone()
            if not user:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '该用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            update_sql = """update zhsq_employee set eState='2',mobile= 's_%s' where eid='%s' """ % (user.mobile,userid)
            se.execute(update_sql.decode('utf-8'))
        else:
            sel_sql = """select * from zhsq_owner where pid = '%s'""" % userid
            user = se.execute(sel_sql.decode('utf-8')).fetchone()
            if not user:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '该用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            update_sql = """update zhsq_owner set status='1' ,pMobile = 's_%s' where pid='%s' """ % (user.pMobile ,userid)
            se.execute(update_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def resetpwd(req):
    try:
        se = settings.DB_SESSION()
        # 获取请求对象
        flag, reqobj = get_reqobj(req)
        if not flag:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8004, 'rspinfo': reqobj}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        # 获取请求要素
        phone =reqobj.get('strPhone','')
        checkcode1= reqobj.get('strCheckcode','')
        newpwd= reqobj.get('strPasswd','')
        usertype = reqobj.get('strType','')
        passwd = get_md5(newpwd)
        sel_sql = """select * from api_checkcode where phonenum = '%s' and status = '0'""" % phone
        checkcodes = se.execute(sel_sql.decode('utf-8')).fetchone()
        if not checkcodes:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '该验证码不存在'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        if checkcodes.checkcode !=checkcode1:
            return HttpResponse(
                sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '验证码错误'}, ensure_ascii=False, encoding='utf-8'),
                content_type='application/json; charset=utf-8')
        if usertype == '0':
            sel_sql = """select * from zhsq_employee where mobile = '%s'""" % phone
            user = se.execute(sel_sql.decode('utf-8')).fetchone()
            if not user:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '该用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')

            update_sql = """update zhsq_employee set password='%s' where mobile='%s' """ % (passwd,phone)
            se.execute(update_sql.decode('utf-8'))
        else:
            sel_sql = """select * from zhsq_owner where pMobile = '%s'""" % phone
            user = se.execute(sel_sql.decode('utf-8')).fetchone()
            if not user:
                return HttpResponse(
                    sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '该用户不存在'}, ensure_ascii=False, encoding='utf-8'),
                    content_type='application/json; charset=utf-8')
            update_sql = """update zhsq_owner set password='%s' where pMobile='%s' """ % (passwd, phone)
            se.execute(update_sql.decode('utf-8'))
        se.close()
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')
    except Exception as e:
        return HttpResponse(
            sjapi.jsondumps({'rspcode': 8999, 'rspinfo': '数据请求失败'}, ensure_ascii=False, encoding='utf-8'),
            content_type='application/json; charset=utf-8')


def go_pay(req):
    app_private_key_string = open(r"D:\docu\2019-3-13\intelligent_community\alipay\private_key.txt").read()
    alipay_public_key_string = open(r"D:\docu\2019-3-13\intelligent_community\alipay\alipay_public_key_sha1.txt").read()

    alipay = AliPay(
        appid="2016092200572398",
        app_notify_url='http://47.104.246.225/api/ensure_pay/',  # 默认回调url
        app_private_key_string=app_private_key_string,
        alipay_public_key_string=alipay_public_key_string,  # 支付宝的公钥，验证支付宝回传消息使用，不是你自己的公钥,
        sign_type="RSA",  # RSA 或者 RSA2
        debug = False  # 默认False
    )

    subject = "电费"

    money = 10000

    # 电脑网站支付，需要跳转到https://openapi.alipay.com/gateway.do? + order_string
    order_string = alipay.api_alipay_trade_page_pay(
        out_trade_no="2019031401021010",
        total_amount=money,
        subject=subject,
        # 付款完成会跳的页面
        return_url="http://47.104.246.225/",
    )
    return HttpResponseRedirect("https://openapi.alipaydev.com/gateway.do?" + order_string)

def ensure_pay(req):
    log.info('ceshi%s' % req)
    return HttpResponse(
        sjapi.jsondumps({'rspcode': 0, 'rspinfo': 'ok'}, ensure_ascii=False, encoding='utf-8'),
        content_type='application/json; charset=utf-8')